package com.sav.common.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.sav.common.catalog.Product;
import com.sav.common.forms.ProductForm;

@Component
public class ProductDAO extends BaseDAO {

	public ProductDAO() {
	}
	
	public Product getProductById(int id) {
		ResultSet resultSet;
		PreparedStatement stm = null;
		try {
			String query = "SELECT * FROM products WHERE idProduct = ?";
			stm = getConnection().prepareStatement(query);
			stm.setInt(1, id);
			resultSet = stm.executeQuery();
			if (!resultSet.next()) return null;
			int idProduct = resultSet.getInt("idProduct");
			int idCategory = resultSet.getInt("idCategory");
			String name = resultSet.getString("name");
			String description = resultSet.getString("description");
			int cost = resultSet.getInt("cost");
			float weight = resultSet.getFloat("weight");
			int quantity = resultSet.getInt("quantity");
			return new Product(idProduct, idCategory, name, description, cost, weight, quantity);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	

	public List<Product> getListProducts() {
		ArrayList<Product> list = new ArrayList<Product>();
		PreparedStatement stm = null;
		try {
			String query = "SELECT * FROM products";
			stm = getConnection().prepareStatement(query);
			ResultSet resultSet = stm.executeQuery();
			while (resultSet.next()) {
				int idProduct = resultSet.getInt("idProduct");
				int idCategory = resultSet.getInt("idCategory");
				String name = resultSet.getString("name");
				String description = resultSet.getString("description");
				int cost = resultSet.getInt("cost");
				float weight = resultSet.getFloat("weight");
				int quantity = resultSet.getInt("quantity");
				list.add(new Product(idProduct, idCategory, name, description, cost, weight, quantity));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public List<Product> getListProductsByCategory(int id, int first, int count) {
		ArrayList<Product> list = new ArrayList<Product>();
		PreparedStatement stm = null;
		try {
			String query = "SELECT * FROM products WHERE idCategory = ? LIMIT ?, ?";
			stm = getConnection().prepareStatement(query);
			stm.setInt(1, id);
			stm.setInt(2, first);
			stm.setInt(3, count);
			ResultSet resultSet = stm.executeQuery();

			while (resultSet.next()) {
				int idCategory = resultSet.getInt("idCategory");
				int idProduct = resultSet.getInt("idProduct");
				String name = resultSet.getString("name");
				String description = resultSet.getString("description");
				int cost = resultSet.getInt("cost");
				float weight = resultSet.getFloat("weight");
				int quantity = resultSet.getInt("quantity");
				list.add(new Product(idProduct, idCategory, name, description, cost, weight, quantity));
			}
			
		} catch (SQLException e) {
			//e.printStackTrace();
			list = null;
		}
		finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}
	
	public void deleteProduct(int id){
		PreparedStatement stm = null;
		try {
			String query = "DELETE FROM products WHERE idProduct = ?";
			stm = getConnection().prepareStatement(query);

			stm.setInt(1, id);
			stm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void changeProduct(ProductForm p) {
		changeProduct(
				Integer.valueOf(p.getIdProduct()), 
				p.getName(), 
				p.getDescription(), 
				p.getCost(), 
				p.getWeight(), 
				p.getQuantity());
	}
	
	
	public void changeProduct(
			int id, 
			String name, 
			String description, 
			String cost, 
			String weight, 
			String quantity) {
		PreparedStatement stm = null;
		try {
			String query = "UPDATE products SET name = ?, description = ?, "
					+ "cost = ?, weight = ?, quantity = ? WHERE idProduct = ?";
			stm = getConnection().prepareStatement(query);
			stm.setString(1, name);
			stm.setString(2, description);
			stm.setString(3, cost);
			stm.setString(4, weight);
			stm.setString(5, quantity);
			stm.setInt(6, id);
			
			stm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	
	}
	
	public int addProduct(ProductForm pf)  {
		
		return addProduct(
				Integer.valueOf(pf.getIdCategory()), 
				pf.getName(), 
				pf.getDescription(), 
				pf.getCost(),
				pf.getWeight(), 
				pf.getQuantity());
	}
	
	
	public int addProduct(
			int idCategory, 
			String name, 
			String description, 
			String cost, 
			String weight, 
			String quantity)  {
		
		PreparedStatement stm = null;
		int id = 0;
		String query = "INSERT INTO products(idCategory, name, description, cost, weight, quantity) "
				+ "VALUES(?, ?, ?, ?, ?, ?)";
		stm = createStatementForInsert(query);
		
		try {
			stm.setInt(1, idCategory);
			stm.setString(2, name);
			stm.setString(3, description);
			stm.setString(4, cost);
			stm.setString(5, weight);
			stm.setString(6, quantity);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		id = executeInsert(stm);
		
		return id;
	}
	
}
